package hotel.dao;

import java.lang.reflect.Constructor;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.nutz.dao.Cnd;
import org.nutz.dao.Condition;
import org.nutz.dao.Dao;
import org.nutz.dao.FieldFilter;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.MappingField;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.DaoUp;
import org.nutz.dao.util.Daos;

import hotel.modal.*;

public class HotelDao {
	
	// ------------------------------------RoomType类---------------------------------------
	
	// 获取所有房间类型信息
	public static List<RoomType> getAllRoomType() {
		Dao dao = DaoUp.me().dao();
		List<RoomTypeDao> rtds = dao.query(RoomTypeDao.class, null); // 查询全部
		ArrayList<RoomType> data = new ArrayList<RoomType>();
		for(RoomTypeDao tmp:rtds) data.add(new RoomType(tmp)); // 遍历转换
		return data;
	}
	
	// 获取房间类型信息 
	public static RoomType getRoomType(int typeId) {
		RoomTypeDao rtd = null;
		Dao dao = DaoUp.me().dao();
		rtd = dao.fetch(RoomTypeDao.class, typeId);
		return new RoomType(rtd);
	}
	
	// 房间类型信息的其他操作
	protected static Boolean opRoomType(RoomType roomType, int type) {
		if(roomType == null) return false;
		RoomTypeDao rtd = (RoomTypeDao)roomType.getDao();
		Dao dao = DaoUp.me().dao();
		Boolean res = false;
		switch(type){
		case 1: 	// 插入 
			if(dao.insert(rtd) != null) res = true; break; 
		case 2:		// 更新
			if(dao.update(rtd) > 0) res = true; break;
		case 3:		// 删除
			if(dao.delete(rtd) > 0) res = true; break;
		default:
			res = false;
		}
		return res;
	}
	
	// 添加房间类型信息
	public static Boolean add(RoomType roomType) {
		return opRoomType(roomType, 1);
	}
	
	// 更新房间类型信息
	public static Boolean update(RoomType roomType) {
		return opRoomType(roomType, 2);
	}
	
	// 删除房间类型信息
	public static Boolean delete(RoomType roomType) {
		return opRoomType(roomType, 3);
	}
	
	// 添加获取下一个房间类型编号
	public static int getNextRoomTypeId() {
		int id = -1;
		Dao dao = DaoUp.me().dao();
		Sql sql = Sqls.create("SELECT max(TypeId) from room_type");
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		id = sql.getInt() + 1;
		return id;
	}
	
	// ------------------------------------Room类---------------------------------------
	
	// 获取所有房间信息
	public static List<Room> getAllRoom() {
		Dao dao = DaoUp.me().dao();
		List<RoomDao> rds = dao.query(RoomDao.class, null); // 查询全部
		ArrayList<Room> data = new ArrayList<Room>();
		for(RoomDao tmp:rds) { 
			tmp = dao.fetchLinks(tmp,  "roomType");
			data.add(new Room(tmp)); // 遍历转换
		}
		return data;
	}
	
	// 获取房间信息
	public static Room getRoom(String roomNo) {
		RoomDao rd = null;
		Dao dao = DaoUp.me().dao();
		rd = dao.fetchLinks(dao.fetch(RoomDao.class, roomNo), "roomType");
		return new Room(rd);
	}
	
	// 根据房间类型获取房间信息 
	public static List<Room> getRoomList(RoomType rt) {
		if(rt == null) return getAllRoom();
		Dao dao = DaoUp.me().dao();
		List<RoomDao> rds = dao.query(RoomDao.class, Cnd.where("Type", "=", rt.getTypeId()));
		ArrayList<Room> data = new ArrayList<Room>();
		for(RoomDao tmp:rds) { 
			tmp.setRoomType((RoomTypeDao)rt.getDao());
			data.add(new Room(tmp)); // 遍历转换
		}
		return data;
	}
	
	// 房间信息的一些操作
	protected static Boolean opRoom(Room room, int type) {
		if (room == null) return false;
		RoomDao rd = (RoomDao) room.getDao();
		Dao dao = DaoUp.me().dao();
		Boolean res = false;
		switch (type) {
		case 1: // 插入
			if (dao.insert(rd) != null) res = true; break;
		case 2: // 更新
			if (dao.update(rd) > 0) res = true; break;
		case 3: // 删除
			if (dao.delete(rd) > 0) res = true; break;
		default:
			res = false;
		}
		return res;
	}
	
	// 添加房间信息
	public static Boolean add(Room room) {
		if(room.getRoomNo() == null) return false;
		return opRoom(room, 1);
	}
	
	// 更新房间信息
	public static Boolean update(Room room) {
		return opRoom(room, 2);
	}
	
	// 删除房间信息
	public static Boolean delete(Room room) {
		return opRoom(room, 3);
	}
	
	// 条件获取房间信息
	public static List<Room> getRoomCondSearch(String col, String cond) {

		String sqlCol = "";
		Dao dao = DaoUp.me().dao();
		
		switch(col) {
		case "房间编号": case "编号":
			sqlCol = "RoomNo";
			break;
		case "房间类型": case "类型":
			sqlCol = "Type";
			break;
		case "房间电话": case "电话":
			sqlCol = "PhoneNum";
			break;
		case "房间位置": case "位置":
			sqlCol = "Position";
			break;
		case "钟点房":
			sqlCol = "HourRoom";
			if(cond.equals("是") || cond.equals("")) cond = "1";
			else if(cond.equals("否") || cond.equals("不是"))  cond = "0";
			break;
		case "床位数量":
			sqlCol = "BedNum";
			break;
		case "需交押金":case "押金":
			sqlCol = "Deposit";
			break;
		case "房间单价":
			sqlCol = "Price";
			break;
		case "员工编号": 
			sqlCol = "Principal";
			break;
		case "房间状态":
			sqlCol = "Status";
			if(cond.equals("可供")) cond="1";
			else if(cond.equals("预定")) cond="2";
			else if(cond.equals("入住")) cond="3";
			else if(cond.equals("脏房")) cond="4";
			else if(cond.equals("停用")) cond="5";
			break;
		default:
			sqlCol = "CONCAT(";	// 拼接所有字段 模糊查询
			List<MappingField> m = dao.getEntity(RoomDao.class).getMappingFields();
			for(int i = 0; i < m.size(); i++) {
				if(i != m.size()-1) sqlCol += m.get(i).getColumnNameInSql() + ",";
				else sqlCol += m.get(i).getColumnNameInSql() + ")";
			}
				break;
		}
		
		Condition c = null;
		if(cond != null && !cond.equals("")) c = Cnd.where(sqlCol, "like", "%" + cond + "%");
		
		List<RoomDao> rds = dao.query(RoomDao.class, c);
		ArrayList<Room> data = new ArrayList<Room>();
		for(RoomDao tmp:rds) { 
			tmp = dao.fetchLinks(tmp,  "roomType");
			data.add(new Room(tmp)); // 遍历转换
		}
		return data;
	}
	
	// 获取所有房间位置信息（去重）
	public static List<String> getAllRoomPosition() {
		Dao dao = DaoUp.me().dao();
		Sql sql = Sqls.create("select distinct position from room_info"); 	  // 硬编码sql	
		/*sql.setCallback(new SqlCallback() {								  // 设置回调函数
	        public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
	           List<String> list = new LinkedList<String>();
	            while (rs.next())
	                list.add(rs.getString("position"));
	            return list; 
	        }
	    });*/
		sql.setCallback(Sqls.callback.strList());						// 直接使用内置Callback
		dao.execute(sql);												// 执行sql语句
		ArrayList<String> data = new ArrayList<String>();
		data.addAll(sql.getList(String.class));							// 获取结果集
		/*Logger log = Logger.getLogger(HotelDao.class);
		for(String tmp:data){
			log.debug(tmp);
		}*/
		return data;
	}
	
	
	// ----------------------------------Employee类------------------------------------- 
	
	// 获取所有员工信息
	public static List<Employee> getAllEmployee() {
		Dao dao = DaoUp.me().dao();
		List<EmployeeDao> eds = dao.query(EmployeeDao.class, null); // 查询全部
		ArrayList<Employee> data = new ArrayList<Employee>();
		for(EmployeeDao tmp:eds) {
			tmp.setBirthday(convDate(tmp.getBirthday()));
			tmp.setEntryTime(convDate(tmp.getEntryTime()));
			data.add(new Employee(tmp));
		}
		return data;
	}
	
	// 获取员工信息
	public static Employee getEmployee(int employeeId) {
		EmployeeDao ed = null;
		Dao dao = DaoUp.me().dao();
		ed = dao.fetch(EmployeeDao.class, employeeId);
		ed.setBirthday(convDate(ed.getBirthday()));
		ed.setEntryTime(convDate(ed.getEntryTime()));
		return new Employee(ed);
	}
	
	// 员工信息的一些操作
	protected static Boolean opEmployee(Employee e, int type) {
		if (e == null)
			return false;
		EmployeeDao ed = (EmployeeDao) e.getDao();
		Dao dao = DaoUp.me().dao();
		Boolean res = false;
		switch (type) {
		case 1: // 插入
			if (dao.insert(ed) != null)
				res = true;
			break;
		case 2: // 更新
			if (dao.update(ed) > 0)
				res = true;
			break;
		case 3: // 删除
			if (dao.delete(ed) > 0)
				res = true;
			break;
		default:
			res = false;
		}
		return res;
	}
	
	// 添加员工信息
	public static Boolean add(Employee employee) {
		if(employee.getEmployeeId() == 0) return false;
		return opEmployee(employee, 1);
	}
	
	// 更新员工信息
	public static Boolean update(Employee employee) {
		return opEmployee(employee, 2);
	}
	
	// 删除员工
	public static Boolean delete(Employee employee) {
		return opEmployee(employee, 3);
	}
	
	// 添加获取下一个员工Id
	public static int getNextEmployeeId() {
		int id = -1;
		Dao dao = DaoUp.me().dao();
		Sql sql = Sqls.create("SELECT max(EmployeeId) from employee");
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		id = sql.getInt() + 1;
		return id;
	}
	
	// 条件获取员工信息
	public static List<Employee> getEmployeeCondSearch(String col, String cond) {
		String sqlCol = "";
		
		Dao dao = DaoUp.me().dao();
		
		if(cond.equals("男")) cond = "M";
		else if(cond.equals("女")) cond = "F";
		else if(cond.equals("在职")) cond = "1";
		else if(cond.equals("离职")) cond = "0";
		
		switch(col) {
		case "员工编号": case "编号":
			sqlCol = "EmployeeId";
			break;
		case "员工姓名": case "姓名":
			sqlCol = "Name";
			break;
		case "员工性别": case "性别":
			sqlCol = "Gender";
			break;
		case "出生日期":
			sqlCol = "Birthday";
			break;
		case "联系电话": case "电话":
			sqlCol = "Phone";
			break;
		case "入职时间":
			sqlCol = "EntryTime";
			break;
		case "身份证号": case "身份证":
			sqlCol = "CertId";
			break;
		case "员工状态": case "状态":
			sqlCol = "Status";
			if(cond.equals("在") || cond.equals("在职")) cond = "1";
			else if(cond.equals("离") || cond.equals("离职")) cond = "0";
			else if(cond.equals("职")) sqlCol = "";
			break;
		case "家庭住址":
			sqlCol = "Address";
			break;
		default:
			sqlCol = "CONCAT(";	// 拼接所有字段 模糊查询
			List<MappingField> m = dao.getEntity(EmployeeDao.class).getMappingFields();
			for(int i = 0; i < m.size(); i++) {
				if(i != m.size()-1) sqlCol += m.get(i).getColumnNameInSql() + ",";
				else sqlCol += m.get(i).getColumnNameInSql() + ")";
			}
				break;
		}
		
		Condition c = null;
		if(cond != null && !cond.equals("")) c = Cnd.where(sqlCol, "like", "%" + cond + "%");
		List<EmployeeDao> eds = dao.query(EmployeeDao.class, c);
		ArrayList<Employee> data = new ArrayList<Employee>();
		for(EmployeeDao tmp:eds) {
			tmp.setBirthday(convDate(tmp.getBirthday()));
			tmp.setEntryTime(convDate(tmp.getEntryTime()));
			data.add(new Employee(tmp));
		}
		return data;
	}
	
	// ------------------------------------User类----------------------------------------
	
	// 获取所有操作用户信息
	public static List<User> getAllUser() {
		Dao dao = DaoUp.me().dao();
		List<UserDao> uds = dao.query(UserDao.class, null); // 查询全部
		ArrayList<User> data = new ArrayList<User>();
		for(UserDao tmp:uds) data.add(new User(tmp)); // 遍历转换
		return data;
	}
	
	// 获取操作用户信息
	public static User getUser(String userName) {
		UserDao ud = null;
		Dao dao = DaoUp.me().dao();
		ud = dao.fetch(UserDao.class, userName);
		return new User(ud);
	}
	
	// 操作用户信息的一些操作
	protected static Boolean opUser(User u, int type) {
		if (u == null) return false;
		UserDao ud = (UserDao) u.getDao();
		Dao dao = DaoUp.me().dao();
		Boolean res = false;
		switch (type) {
		case 1: // 插入
			if (dao.insert(ud) != null)
				res = true;
			break;
		case 2: // 更新
			if (dao.update(ud) > 0)
				res = true;
			break;
		case 3: // 删除
			if (dao.delete(ud) > 0)
				res = true;
			break;
		default:
			res = false;
		}
		return res;
	}
	
	// 添加操作用户信息
	public static Boolean add(User user) {
		return opUser(user, 1);
	}
	
	// 更新操作用户信息
	public static Boolean update(User user) {
		return opUser(user, 2);
	}
	
	// 删除操作用户信息
	public static Boolean delete(User user) {
		return opUser(user, 3);
	}
	
	// 修改操作用户密码
	public static Boolean changePassword(User user) {
		if (user == null) return true;
		UserDao ud = (UserDao) user.getDao();
		Dao dao = DaoUp.me().dao();
		FieldFilter ff = FieldFilter.create(UserDao.class, "^password$"); // 过滤,只更新password字段
		if(Daos.ext(dao, ff).update(ud) > 0) return true;
		else return false;
	}
	
	// ------------------------------------CustomerType类---------------------------------------
	
	// 获取所有会员类型信息
	public static List<CustomerType> getAllCustomerType() {
		Dao dao = DaoUp.me().dao();
		List<CustomerTypeDao> ctds = dao.query(CustomerTypeDao.class, null); // 查询全部
		ArrayList<CustomerType> data = new ArrayList<CustomerType>();
		for(CustomerTypeDao tmp:ctds) data.add(new CustomerType(tmp)); // 遍历转换
		return data;
	}
	
	// 会员类型信息的一些操作
	protected static Boolean opCustomerType(CustomerType ct, int type) {
		if (ct == null) return false;
		CustomerTypeDao ctd = (CustomerTypeDao) ct.getDao();
		Dao dao = DaoUp.me().dao();
		Boolean res = false;
		switch (type) {
		case 1: // 插入
			if (dao.insert(ctd) != null)
				res = true;
			break;
		case 2: // 更新
			if (dao.update(ctd) > 0)
				res = true;
			break;
		case 3: // 删除
			if (dao.delete(ctd) > 0)
				res = true;
			break;
		default:
			res = false;
		}
		return res;
	}
	
	// 添加会员类型信息
	public static Boolean add(CustomerType customerType) {
		return opCustomerType(customerType, 1);
	}
	
	// 更新会员类型信息
	public static Boolean update(CustomerType customerType) {
		return opCustomerType(customerType, 2);
	}
	
	// 删除会员类型信息
	public static Boolean delete(CustomerType customerType) {
		return opCustomerType(customerType, 3);
	}
	
	// ------------------------------------Customer类---------------------------------------
	
	// 获取所有客户信息
	public static List<Customer> getAllCutomer() {
		return getAll(Customer.class, "customerType");
	}
	
	// 获取客户信息列表（分页）
	public static QueryResult getCustomerList(int pageNumber, int pageSize){ 
		// pageNumber表示页数 ,从 1 开始。pageSize表示每页记录大小
		Dao dao = DaoUp.me().dao();
	    Pager pager = dao.createPager(pageNumber, pageSize);
	    List<CustomerDao> lds = dao.query(CustomerDao.class, null, pager);
	    ArrayList<Customer> list = new ArrayList<Customer>();
		for(CustomerDao tmp:lds) {				// 将CustomerDao类对象转换成Customer类对象
			tmp = dao.fetchLinks(tmp,  "customerType"); // 获取CustomerType中的内容
			list.add(new Customer(tmp));
		}
	    pager.setRecordCount(dao.count(CustomerDao.class)); // Pager需要手动设置记录数量
	    return new QueryResult(list, pager);
	}
	
	// 获取客户信息(证件号码方式)
	public static Customer getCustomer(String certId) {
		CustomerDao cd = null;
		Dao dao = DaoUp.me().dao();
		cd = dao.fetch(CustomerDao.class, Cnd.where("CertId", "=", certId));
		cd = dao.fetchLinks(cd, "customerType");
		if(cd == null) return null;
		else return new Customer(cd);
	}
	
	// 获取客户信息
	public static Customer getCustomer(int customerNo) {
		return getFormInt(Customer.class, customerNo, "customerType");
	}
	
	// 添加客户信息
	public static Boolean add(Customer customer) {
		return opDaoClass(customer.getDao(), 1);
	}

	// 更新客户信息
	public static Boolean update(Customer customer) {
		return opDaoClass(customer.getDao(), 2);
	}

	// 删除客户信息
	public static Boolean delete(Customer customer) {
		return opDaoClass(customer.getDao(), 3);
	}
	
	// 条件获取客户信息（分页）
	public static QueryResult getCustomerCondSearch(String col, String cond, int pageNumber, int pageSize) {
		String sqlCol = "";

		Dao dao = DaoUp.me().dao();

		switch (col) {
		case "客户编号": case "编号":
			sqlCol = "CustomerNo";
			break;
		case "客户姓名": case "姓名":
			sqlCol = "Name";
			break;
		case "证件号码":
			sqlCol = "CertId";
			break;
		case "联系电话":
			sqlCol = "Phone";
			break;
		default:
			sqlCol = "CONCAT(customerNo,name,certId,phone)"; // 拼接字段 模糊查询
			break;
		}

		Condition c = null;
		if(cond != null && !cond.equals("")) c = Cnd.where(sqlCol, "like", "%" + cond + "%");
		Pager pager = dao.createPager(pageNumber, pageSize);
		List<CustomerDao> lds = dao.query(CustomerDao.class, c, pager);
		ArrayList<Customer> list = new ArrayList<>();
		for(CustomerDao tmp:lds) {						// 将CustomerDao类对象转换成Customer类对象
			tmp = dao.fetchLinks(tmp,  "customerType"); // 获取CustomerType中的内容
			list.add(new Customer(tmp));
		}
		pager.setRecordCount(dao.count(CustomerDao.class, c)); // Pager需要手动设置记录数量
	    return new QueryResult(list, pager);
	}
	
	// ------------------------------------Goods类---------------------------------------
	
	// 获取所有物品信息
	public static List<Goods> getAllGoods() {
		return getAll(Goods.class);
	}
	
	// 获取物品信息 
	public static Goods getGoods(int goodsId) {
		return getFormInt(Goods.class, goodsId);
	}
	
	// 添加物品信息 
	public static Boolean add(Goods goods) {
		return opDaoClass((GoodsDao)(goods.getDao()), 1);
	}

	// 更新物品信息 
	public static Boolean update(Goods goods) {
		return opDaoClass((GoodsDao)(goods.getDao()), 2);
	}

	// 删除物品信息 
	public static Boolean delete(Goods goods) {
		return opDaoClass((GoodsDao)(goods.getDao()), 3);
	}
	
	// ------------------------------------Order类---------------------------------------
	
	// 获取所有结账单信息
	public static List<Order> getAllOrder() {
		return getAll(Order.class, Cnd.NEW().desc("OrderId"));
	}
	
	// 分页获取结账单信息
	public static QueryResult getOrderList(int pageNumber, int pageSize) {
		return getList(Order.class, pageNumber, pageSize, Cnd.NEW().desc("OrderId"));
	}
	
	// 获取结账单信息
	public static Order getOrder(String orderId) {
		return getFormString(Order.class, orderId);
	}

	// 添加结账单信息
	public static Boolean add(Order order) {
		return opDaoClass((OrderDao)(order.getDao()), 1);
	}

	// 更新结账单信息
	public static Boolean update(Order order) {
		return opDaoClass((OrderDao)(order.getDao()), 2);
	}

	// 删除结账单信息
	public static Boolean delete(Order order) {
		return opDaoClass((OrderDao)(order.getDao()), 3);
	}
	
	// 条件获取账单信息（分页）
	public static QueryResult getOrderCondSearch(String col, String cond, int pageNumber, int pageSize) {
		String sqlCol = "";

		Dao dao = DaoUp.me().dao();

		switch (col) {
		case "结账单号":
		case "单号":
			sqlCol = "OrderId";
			break;
		case "房间号":
			sqlCol = "RoomNo";
			break;
		case "客户姓名":
			sqlCol = "Name";
			break;
		case "联系电话":
			sqlCol = "Phone";
			break;
		default:
			sqlCol = "CONCAT(OrderId,RoomNo,Name,Phone)";
			break;
		}

		Condition c = null;
		if (cond != null && !cond.equals(""))
			c = Cnd.where(sqlCol, "like", "%" + cond + "%").desc("OrderId");
		if (c == null)
			c = Cnd.NEW().desc("OrderId");

		Pager pager = dao.createPager(pageNumber, pageSize);
		List<OrderDao> ods = null;
		if (sqlCol.equals("Name") || sqlCol.equals("Phone") || sqlCol.equals("CONCAT(OrderId,RoomNo,Name,Phone)")) {
			// http://www.nutzam.com/core/dao/customized_sql.html#ndoc-6
			Sql sql = Sqls.create("Select order.* from order join room_rent "+
					"on order.PreOrderId=order.PreOrderId join customer on"
					+ " customer.CustomerNo=room_rent.CustomerNo  $condition"); // $condition占位
			sql.setCondition(c);
			sql.setPager(pager);
			// Sqls.callback.entities() 从 ResultSet获取一组对象的回调对象
			sql.setCallback(Sqls.callback.entities());
			sql.setEntity(dao.getEntity(OrderDao.class));
			dao.execute(sql);
			ods = sql.getList(OrderDao.class);
			pager.setRecordCount((int) Daos.queryCount(dao, sql)); // queryCount查询某sql的结果条数
		} else {
			ods = dao.query(OrderDao.class, c, pager);
			pager.setRecordCount(dao.count(OrderDao.class, c)); // Pager需要手动设置记录数量
		}

		ArrayList<Order> list = new ArrayList<>();
		for (OrderDao tmp : ods)
			list.add(new Order(tmp));
		return new QueryResult(list, pager);
	}
	
	public static Boolean cancel(Order order) {
		if (order == null) return true;
		OrderDao od = (OrderDao) order.getDao();
		if(od == null) return false;
		Dao dao = DaoUp.me().dao();
		FieldFilter ff = FieldFilter.create(OrderDao.class, "^valid$"); // 过滤,只更新valid字段
		if (Daos.ext(dao, ff).update(od) > 0)
			return true;
		else
			return false;
	}
	
	// ------------------------------------RoomChange类---------------------------------------
	
	// 获取所有换房信息
	public static List<RoomChange> getAllRoomChange() {
		return getAll(RoomChange.class, Cnd.NEW().desc("ChangeId"));
	}
		
	// 分页获取换房信息
	public static QueryResult getRoomChangeList(int pageNumber, int pageSize) {
		return getList(RoomChange.class, pageNumber, pageSize, Cnd.NEW().desc("ChangeId"));
	}

	// 获取换房信息
	public static RoomChange getRoomChange(int changeId) {
		return getFormInt(RoomChange.class, changeId);
	}

	// 添加换房信息
	public static Boolean add(RoomChange roomChange) {
		return opDaoClass((RoomChangeDao)(roomChange.getDao()), 1);
	}

	// 更新换房信息
	public static Boolean update(RoomChange roomChange) {
		return opDaoClass((RoomChangeDao)(roomChange.getDao()), 2);
	}

	// 删除换房信息
	public static Boolean delete(RoomChange roomChange) {
		return opDaoClass((RoomChangeDao)(roomChange.getDao()), 3);
	}
	
	// 条件获取换房信息（分页）
	public static QueryResult getRoomChangeCondSearch(String col, String cond, int pageNumber, int pageSize) {
		String sqlCol = "";

		Dao dao = DaoUp.me().dao();

		switch (col) {
		case "入住单号":
		case "单号":
			sqlCol = "PreOrderId";
			break;
		case "房间号":
			sqlCol = "CONCAT(PreRoomNo,' ',NowRoomNo)";
			break;
		default:
			sqlCol = "CONCAT(PreOrderId,' ',PreRoomNo,' ',NowRoomNo)";
			break;
		}

		Condition c = null;
		if (cond != null && !cond.equals(""))
			c = Cnd.where(sqlCol, "like", "%" + cond + "%").desc("ChangeTime");
		if (c == null)
			c = Cnd.NEW().desc("ChangeTime");

		Pager pager = dao.createPager(pageNumber, pageSize);
		List<RoomChangeDao> rcds = null;

		rcds = dao.query(RoomChangeDao.class, c, pager);
		pager.setRecordCount(dao.count(RoomChangeDao.class, c)); // Pager需要手动设置记录数量

		ArrayList<RoomChange> list = new ArrayList<>();
		for (RoomChangeDao tmp : rcds)
			list.add(new RoomChange(tmp));
		return new QueryResult(list, pager);
	}
	
	// ------------------------------------RoomGoods类---------------------------------------
	
	// 获取所有房间物品信息
	public static List<RoomGoods> getAllRoomGoods() {
		return getAll(RoomGoods.class);
	}
		
	// 分页获取房间物品信息
	public static QueryResult getRoomGoodsList(int pageNumber, int pageSize) {
		return getList(RoomGoods.class, pageNumber, pageSize);
	}

	// 获取房间物品信息
	public static RoomGoods getRoomGoods(int roomGoodsId) {
		return getFormInt(RoomGoods.class, roomGoodsId);
	}

	// 添加房间物品信息
	public static Boolean add(RoomGoods roomGoods) {
		return opDaoClass((RoomGoodsDao)(roomGoods.getDao()), 1);
	}

	// 更新房间物品信息
	public static Boolean update(RoomGoods roomGoods) {
		return opDaoClass((RoomGoodsDao)(roomGoods.getDao()), 2);
	}

	// 删除房间物品信息
	public static Boolean delete(RoomGoods roomGoods) {
		return opDaoClass((RoomGoodsDao)(roomGoods.getDao()), 3);
	}
	
	// ------------------------------------RoomRent类---------------------------------------
	
	// 获取所有开房信息
	public static List<RoomRent> getAllRoomRent() {
		return getAll(RoomRent.class, Cnd.NEW().desc("PreOrderId"));
	}

	// 分页获取开房信息
	public static QueryResult getRoomRentList(int pageNumber, int pageSize) {
		return getList(RoomRent.class, pageNumber, pageSize, Cnd.NEW().desc("PreOrderId"));
	}

	// 获取开房信息
	public static RoomRent getRoomRent(String preOrderId) {
		return getFormString(RoomRent.class, preOrderId);
	}

	// 添加开房信息
	public static Boolean add(RoomRent roomRent) {
		return opDaoClass((RoomRentDao)(roomRent.getDao()), 1);
	}

	// 更新开房信息
	public static Boolean update(RoomRent roomRent) {
		return opDaoClass((RoomRentDao)(roomRent.getDao()), 2);
	}

	// 删除开房信息
	public static Boolean delete(RoomRent roomRent) {
		return opDaoClass((RoomRentDao)(roomRent.getDao()), 3);
	}
	
	// 根据房间号获取开房信息
	public static RoomRent getRoomRentFromRoom(String roomNo) {
		Dao dao = DaoUp.me().dao();
		if(roomNo.isEmpty()) return null;
		Condition c = Cnd.where("RoomNo", "=", roomNo).desc("CreateTime");
		RoomRentDao rrd = dao.fetch(RoomRentDao.class, c);
		return new RoomRent(rrd);
	}
	
	// 条件获取开房信息（分页）
	public static QueryResult getRoomRentCondSearch(String col, String cond, int pageNumber, int pageSize) {
		String sqlCol = "";

		Dao dao = DaoUp.me().dao();

		switch (col) {
		case "入住单号":
		case "单号":
			sqlCol = "PreOrderId";
			break;
		case "房间号":
			sqlCol = "RoomNo";
			break;
		case "客户姓名":
			sqlCol = "Name";
			break;
		case "联系电话":
			sqlCol = "Phone";
			break;
		default:
			sqlCol = "CONCAT(PreOrderId,RoomNo,Name,Phone)";
			break;
		}
		
		Condition c = null;
		if (cond != null && !cond.equals(""))
			c = Cnd.where(sqlCol, "like", "%" + cond + "%").desc("PreOrderId");
		if(c == null) c = Cnd.NEW().desc("PreOrderId");
		
		Pager pager = dao.createPager(pageNumber, pageSize);
		List<RoomRentDao> lds = null;
		if(sqlCol.equals("Name") || sqlCol.equals("Phone") || sqlCol.equals("CONCAT(PreOrderId,RoomNo,Name,Phone)")) {
			// http://www.nutzam.com/core/dao/customized_sql.html#ndoc-6
			Sql sql = Sqls.create("Select room_rent.* from room_rent join customer on"+
					" customer.CustomerNo=room_rent.CustomerNo $condition");  // $condition占位
			sql.setCondition(c);
			sql.setPager(pager);
			//Sqls.callback.entities() 从 ResultSet获取一组对象的回调对象
			sql.setCallback(Sqls.callback.entities()); 
			sql.setEntity(dao.getEntity(RoomRentDao.class));
			dao.execute(sql);
			lds = sql.getList(RoomRentDao.class);
			pager.setRecordCount((int) Daos.queryCount(dao, sql)); // queryCount查询某sql的结果条数
		}else {
			lds = dao.query(RoomRentDao.class, c, pager);
			pager.setRecordCount(dao.count(RoomRent.class, c)); // Pager需要手动设置记录数量
		}
		
		ArrayList<RoomRent> list = new ArrayList<>();
		for (RoomRentDao tmp : lds) list.add(new RoomRent(tmp));
		return new QueryResult(list, pager);
	}
	
	public static Boolean cancel(RoomRent roomRent) {
		if (roomRent == null) return true;
		RoomRentDao rd = (RoomRentDao) roomRent.getDao();
		if(rd == null) return false;
		Dao dao = DaoUp.me().dao();
		FieldFilter ff = FieldFilter.create(RoomRentDao.class, "^valid$"); // 过滤,只更新valid字段
		if (Daos.ext(dao, ff).update(rd) > 0)
			return true;
		else
			return false;
	}
	
	// ------------------------------------RoomReserve类---------------------------------------
	
	// 获取所有客房预定信息
	public static List<RoomReserve> getAllRoomReserve() {
		return getAll(RoomReserve.class, Cnd.NEW().desc("ReserveId"));
	}

	// 分页获取客房预定信息
	public static QueryResult getRoomReserveList(int pageNumber, int pageSize) {
		return getList(RoomReserve.class, pageNumber, pageSize, Cnd.NEW().desc("ReserveId"));
	}

	// 获取客房预定信息
	public static RoomReserve getRoomReserve(String reserveId) {
		return getFormString(RoomReserve.class, reserveId);
	}

	// 添加客房预定信息
	public static Boolean add(RoomReserve roomReserve) {
		return opDaoClass((RoomReserveDao)(roomReserve.getDao()), 1);
	}

	// 更新客房预定信息
	public static Boolean update(RoomReserve roomReserve) {
		return opDaoClass((RoomReserveDao)(roomReserve.getDao()), 2);
	}

	// 删除客房预定信息
	public static Boolean delete(RoomReserve roomReserve) {
		return opDaoClass((RoomReserveDao)(roomReserve.getDao()), 3);
	}
	
	public static Boolean cancel(RoomReserve roomReserve) {
		if (roomReserve == null) return true;
		RoomReserveDao ud = (RoomReserveDao) roomReserve.getDao();
		Dao dao = DaoUp.me().dao();
		FieldFilter ff = FieldFilter.create(RoomReserveDao.class, "^valid$"); // 过滤,只更新valid字段
		if (Daos.ext(dao, ff).update(ud) > 0)
			return true;
		else
			return false;
	}
	
	// 条件获取客房预定信息（分页）
	public static QueryResult getReserveCondSearch(String col, String cond, int pageNumber, int pageSize) {
		String sqlCol = "";
		Dao dao = DaoUp.me().dao();
		
		if(cond.equals("有效"))
			cond = "true";
		else if(cond.equals("无效"))
			cond = "false";

		switch (col) {
		case "预定单号": case "单号":
			sqlCol = "ReserveId";
			break;
		case "预定房间": case "房间":
			sqlCol = "RoomNo";
			break;
		case "联系人":
			sqlCol = "Name";
			break;
		case "联系电话":
			sqlCol = "Phone";
			break;
		default:
			sqlCol = "CONCAT("; // 拼接所有字段 模糊查询
			List<MappingField> m = dao.getEntity(RoomReserveDao.class).getMappingFields();
			
			for (int i = 0; i < m.size(); i++) {
				
				if (i != m.size() - 1)
					sqlCol += m.get(i).getColumnNameInSql() + ",";
				else
					sqlCol += m.get(i).getColumnNameInSql() + ")";
			}
			break;
		}

		Condition c = null;
		if(cond != null && !cond.equals("")) c = Cnd.where(sqlCol, "like", "%" + cond + "%").desc("ReserveId");
		if(c == null) c = Cnd.NEW().desc("ReserveId");
		Pager pager = dao.createPager(pageNumber, pageSize);
		List<RoomReserveDao> rrds = dao.query(RoomReserveDao.class, c, pager);
		ArrayList<RoomReserve> list = new ArrayList<>();
		for (RoomReserveDao tmp : rrds) list.add(new RoomReserve(tmp));
		pager.setRecordCount(dao.count(RoomReserveDao.class, c)); // Pager需要手动设置记录数量
	    return new QueryResult(list, pager);
	}
	
	// 根据房间号获取预定信息
	public static RoomReserve getRoomReserveFromRoom(String roomNo) {
		Dao dao = DaoUp.me().dao();
		if (roomNo.isEmpty()) return null;
		Condition c = Cnd.where("RoomNo", "=", roomNo).desc("CreateTime");
		RoomReserveDao rrd = dao.fetch(RoomReserveDao.class, c);
		return new RoomReserve(rrd);
	}
	// ------------------------------------统计---------------------------------------
	
	public static int countReserveInfo(String start, String end) {
		return countInfo(RoomReserve.class, start, end);
	}
	
	public static int countRentInfo(String start, String end) {
		return countInfo(RoomRent.class, start, end);
	}
	
	public static int countOrderInfo(String start, String end) {
		return countInfo(Order.class, start, end);
	}
	
	protected static <T>int countInfo(java.lang.Class<T> classOfT, String start, String end) {
		Class<?> clasz = getDaoClass(classOfT.getSimpleName());
		Dao dao = DaoUp.me().dao();	
		Condition c = Cnd.where("CreateTime", ">=", start).and("CreateTime", "<=", end);
		return dao.count(clasz, c);	
	}
	
	public static double countRealCharge(String start, String end) {
		return countCharge("realCharge", start, end);
	}
	
	public static double countBasicCharge(String start, String end) {
		return countCharge("basicCost", start, end);
	}
	
	public static double countOtherCharge(String start, String end) {
		return countCharge("otherCost", start, end);
	}
	
	protected static double countCharge(String fieldName, String start, String end) {
		Dao dao = DaoUp.me().dao();	
		Condition c = Cnd.where("CreateTime", ">=", start).and("CreateTime", "<=", end).and("Valid", "=", "1");
		BigDecimal bd = (BigDecimal)dao.func2(OrderDao.class, "sum", fieldName, c);
		if(bd != null) {
			return bd.doubleValue();
		}else return 0.0;
	}
	
	public static int countRoomStatus(int status) {
		Dao dao = DaoUp.me().dao();	
		Condition c = Cnd.where("Status", "=", String.valueOf(status));
		return dao.count(RoomDao.class, c);	
	}
	
	// ------------------------------------泛型模板---------------------------------------
	
	// 获取所有
	public static <T>List<T> getAll(java.lang.Class<T> classOfT) {
		return getAllObj(classOfT, null, null);
	}
	
	// 获取所有，带（排序）条件
	public static <T> List<T> getAll(java.lang.Class<T> classOfT, Condition c) {
		return getAllObj(classOfT, null, c);
	}
	
	// 获取所有 （有一对一的情况）
	public static <T>List<T> getAll(java.lang.Class<T> classOfT, String arg) {
		if(arg != null && !arg.isEmpty()) {
			return getAllObj(classOfT, arg, null);
		}else {
			return getAllObj(classOfT, null, null);
		}
	}
	
	// 获取所有 （有一对一的情况），带（排序）条件
	public static <T> List<T> getAll(java.lang.Class<T> classOfT, String arg, Condition c) {
		if (arg != null && !arg.isEmpty()) {
			return getAllObj(classOfT, arg, c);
		} else {
			return getAllObj(classOfT, null, c);
		}
	}
	
	protected static <T>List<T> getAllObj(java.lang.Class<T> classOfT, String arg, Condition c) {
		Dao dao = DaoUp.me().dao();
		Class<?> clasz = getDaoClass(classOfT.getSimpleName()); // getSimpleName获取类名不带包名
		List<?> lists = dao.query(clasz, c); // 查询全部
		ArrayList<T> data = new ArrayList<T>();
		for(int i = 0; i < lists.size(); i++) {
			Constructor<T> constructor;
			try {
				T t = null;
				constructor = classOfT.getConstructor(clasz);
				if(arg != null && !arg.isEmpty()) {
					t = constructor.newInstance(dao.fetchLinks(lists.get(i), arg));
				}else {
					t = constructor.newInstance(lists.get(i));
				}
				data.add(t);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return data;
	}
	
	// 获取列表（分页） 有一对一的情况
	public static <T> QueryResult getList(java.lang.Class<T> classOfT, int pageNumber, int pageSize, String arg){
		return getObjList(classOfT, pageNumber, pageSize, arg, null);
	}
	
	// 获取列表（分页） 有一对一的情况, 有条件
	public static <T> QueryResult getList(java.lang.Class<T> classOfT, int pageNumber, int pageSize, String arg, Condition c) {
		return getObjList(classOfT, pageNumber, pageSize, arg, c);
	}
	
	// 获取列表（分页）
	public static <T> QueryResult getList(java.lang.Class<T> classOfT, int pageNumber, int pageSize){
		return getObjList(classOfT, pageNumber, pageSize, null, null);
	}
	
	// 获取列表（分页）, 有条件
	public static <T> QueryResult getList(java.lang.Class<T> classOfT, int pageNumber, int pageSize, Condition c) {
		return getObjList(classOfT, pageNumber, pageSize, null, c);
	}
	
	public static <T> QueryResult getObjList(java.lang.Class<T> classOfT, int pageNumber, int pageSize, String arg, Condition c){ 
		// pageNumber表示页数 ,从 1 开始。pageSize表示每页记录大小
		Dao dao = DaoUp.me().dao();
		Pager pager = dao.createPager(pageNumber, pageSize);
		Class<?> clasz = getDaoClass(classOfT.getSimpleName()); // getSimpleName获取类名不带包名
		List<?> lists = dao.query(clasz, c, pager); 			// 分页查询
		ArrayList<T> data = new ArrayList<T>();
		for(int i = 0; i < lists.size(); i++) {
			Constructor<T> constructor;
			try {
				T t = null;
				constructor = classOfT.getConstructor(clasz);
				if(arg != null && !arg.isEmpty()) {
					t = constructor.newInstance(dao.fetchLinks(lists.get(i), arg));
				}else {
					t = constructor.newInstance(lists.get(i));
				}
				data.add(t);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		pager.setRecordCount(dao.count(clasz, c)); // Pager需要手动设置记录数量
		return new QueryResult(data, pager);
	}
	
	// 获取一个 (String主键)
	protected static <T> T getFormString(java.lang.Class<T> classOfT, String id, String arg) {
		return getObjFromString(classOfT, id, arg);
	}
	
	// 获取一个 (String主键)
	protected static <T> T getFormString(java.lang.Class<T> classOfT, String id) {
			return getObjFromString(classOfT, id, null);
	}
	
	// 获取一个 (int主键)
	protected static <T> T getFormInt(java.lang.Class<T> classOfT, int id, String arg) {
		return getObjFromInt(classOfT, id, arg);
	}
		
	// 获取一个 (int主键)
	protected static <T> T getFormInt(java.lang.Class<T> classOfT, int id) {
			return getObjFromInt(classOfT, id, null);
	}
	
	protected static <T> T getObjFromString(java.lang.Class<T> classOfT, String id, String arg) {
		Dao dao = DaoUp.me().dao();
		Class<?> c = getDaoClass(classOfT.getSimpleName()); // getSimpleName获取类名不带包名
		Constructor<T> constructor;
		T t = null;
		try {
			constructor = classOfT.getConstructor(c);
			if(arg != null && !arg.isEmpty()) {
				t = constructor.newInstance(dao.fetchLinks(dao.fetch(c, id), arg));
			}else {
				t = constructor.newInstance(dao.fetch(c, id));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return t;
	}
	
	protected static <T> T getObjFromInt(java.lang.Class<T> classOfT, Integer id, String arg) {
		Dao dao = DaoUp.me().dao();
		Class<?> c = getDaoClass(classOfT.getSimpleName()); // getSimpleName获取类名不带包名
		Constructor<T> constructor;
		T t = null;
		try {
			constructor = classOfT.getConstructor(c);
			if (arg != null && !arg.isEmpty()) {
				t = constructor.newInstance(dao.fetchLinks(dao.fetch(c, id), arg));
			} else {
				t = constructor.newInstance(dao.fetch(c, id));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return t;
	}
	
	// 一些操作
	protected static <T>Boolean opDaoClass(T t, int type) {
		if (t == null) return false;
		Dao dao = DaoUp.me().dao();
		Boolean result = false;
		switch (type) {
		case 1: // 插入
			if (dao.insert(t) != null)
				result = true;
			break;
		case 2: // 更新
			if (dao.update(t) > 0)
				result = true;
			break;
		case 3: // 删除
			if (dao.delete(t) > 0)
				result = true;
			break;
		default:
			result = false;
		}
		return result;
	}
	
	protected static Class<?> getDaoClass(String className) {
		Class<?> c = null;
		try {
			Class<?> nowClass = new Object() { // 获取当前类
				@SuppressWarnings("unused")
				public String getClassName()
				{
					String clazzName = this.getClass().getName();
					return clazzName.substring(0, clazzName.lastIndexOf('$'));
				}
			}.getClass(); 
			String packageName = nowClass.getPackage().getName(); // 获取当前包
			c = Class.forName(packageName + "." + className + "Dao");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return c;
	}

	// ------------------------------------其他函数---------------------------------------
	
	// 将日期适配成yyyy-MM-dd格式的String日期
	public static String convDate(String strDate) { 
		String str = null;
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		try {
			str = df.format(df.parse(strDate));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return str;
	}
	
	public static java.sql.Date strToDate(String strDate) {
		String str = strDate;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		java.util.Date d = null;
		try {
			d = format.parse(str);
		} catch (Exception e) {
			e.printStackTrace();
		}
		java.sql.Date date = new java.sql.Date(d.getTime());
		return date;
	}

	public static java.sql.Time strToTime(String strDate) {
		String str = strDate;
		SimpleDateFormat format = new SimpleDateFormat("hh:mm:ss");
		java.util.Date d = null;
		try {
			d = format.parse(str);
		} catch (Exception e) {
			e.printStackTrace();
		}
		java.sql.Time time = new java.sql.Time(d.getTime());
		return time;
	}
}
